"""This file handles database operations."""
from tools.mysqlconntool import mysqlconn
def create_tables():
    mydb = mysqlconn.get_connection()
    mycursor = mydb.cursor()

    # 创建 users 表
    sql = """
    CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL COMMENT '姓名',
        identity_card VARCHAR(255) NOT NULL COMMENT '身份证号码',
        identity_card_sha VARCHAR(255) COMMENT '身份证号码散列',
        admission_no VARCHAR(255) NOT NULL COMMENT '住院号',
        gender ENUM('MALE', 'FEMALE') NOT NULL COMMENT '性别',
        age VARCHAR(255) NOT NULL COMMENT '年龄',
        fertility_history TEXT NOT NULL COMMENT '生育史',
        education_level ENUM('primary', 'secondary', 'higher') NOT NULL COMMENT '教育程度',
        occupation VARCHAR(255) NOT NULL COMMENT '职业',
        diagnosis TEXT NOT NULL COMMENT '诊断',
        comorbidity VARCHAR(255) NOT NULL COMMENT '合并疾病',
        therapeutic_modality VARCHAR(255) NOT NULL COMMENT '治疗方式',
        methods_of_medical_cost_coverage VARCHAR(255) NOT NULL COMMENT '医疗费用承担方式',
        marital_status ENUM('single', 'married', 'divorced') NOT NULL COMMENT '婚姻状况',
        phone VARCHAR(255) NOT NULL COMMENT '电话',
        emergency_contact VARCHAR(255) NOT NULL COMMENT '紧急联系人',
        emergency_contact_phone VARCHAR(255) NOT NULL COMMENT '紧急联系人电话',
        address VARCHAR(255) NOT NULL COMMENT '家庭住址',
        agricultural_indicator BOOLEAN NOT NULL COMMENT '农业户口',
        temperature VARCHAR(255)  NOT NULL COMMENT '体温',
        pulse VARCHAR(255)  NOT NULL COMMENT '心率',
        respiration_rate VARCHAR(255)  NOT NULL COMMENT '呼吸频率',
        blood_pressure VARCHAR(255)  NOT NULL COMMENT '血压',
        create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
    )
    """
    mycursor.execute(sql)

    # 创建 user_psychological_test_record 表
    sql = """
    CREATE TABLE IF NOT EXISTS user_psychological_test_record (
        id INT AUTO_INCREMENT PRIMARY KEY COMMENT '记录id',
        user_id INT NOT NULL COMMENT '用户id',
        pss_score VARCHAR(255)  NULL COMMENT '应激感受量表评分perceived_stress_scale',
        gad_score VARCHAR(255)  NULL COMMENT '广泛性焦虑量表评分Generalized Anxiety Disorder Scale',
        pswq_score VARCHAR(255)  NULL COMMENT '宾州担忧问卷Pennsylvania Manifest Anxiety Scale',
        phq_score VARCHAR(255)  NULL COMMENT '患者健康问卷抑郁量表评分patient_health_questionnaire',
        rrs_score VARCHAR(255)  NULL COMMENT '反刍思维量表评分rumination_response_scale',
        prmq_score VARCHAR(255)  NULL COMMENT '前瞻回溯记忆问卷Prospective and Retrospective Memory Questionnaire',
        qlq_score JSON  NULL COMMENT '生命质量核心量表QLQ-C30',
        psqi_score VARCHAR(255)  NULL COMMENT '匹兹堡睡眠质量指数Pittsburgh Sleep Quality Index',
        create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        FOREIGN KEY (user_id) REFERENCES users(id)
    )
    """
    mycursor.execute(sql)

    mycursor.close()
    mydb.close()

create_tables()
